Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER ROLLBACK SEGMENT

Note: Oracle strongly recommends that you run your database in automatic undo management mode instead of using rollback segments. Do not use rollback segments unless you must do so for compatibility with earlier versions of Oracle Database. Please refer to Oracle Database Administrator's Guide for information on automatic undo management.

Use the `ALTER ROLLBACK SEGMENT` statement to bring a rollback segment online or offline, change its storage characteristics, or shrink it to an optimal or specified size.

The information in this section assumes that your database is running in rollback undo mode (the `UNDO_MANAGEMENT` initialization parameter is set to `MANUAL` or not set at all). If your database is running in automatic undo mode (the `UNDO_MANAGEMENT` initialization parameter is set to `AUTO`), then user-created rollback segments are irrelevant.

See Also: CREATE ROLLBACK SEGMENT for information on creating a rollback segment Oracle Database Reference for information on the `UNDO_MANAGEMENT` parameter

Prerequisites

You must have the `ALTER ROLLBACK SEGMENT` system privilege.

Syntax

alter_rollback_segment::=

Description of the illustration alter_rollback_segment.gif

(storage_clause, size_clause::=)

Semantics

rollback_segment

Specify the name of an existing rollback segment.

ONLINE

Specify `ONLINE` to bring the rollback segment online. When you create a rollback segment, it is initially offline and not available for transactions. This clause brings the rollback segment online, making it available for transactions by your instance. You can also bring a rollback segment online when you start your instance with the initialization parameter `ROLLBACK_SEGMENTS`.

See Also: "Bringing a Rollback Segment Online: Example"

OFFLINE

Specify `OFFLINE` to take the rollback segment offline.

- If the rollback segment does not contain any information needed to roll back an active transaction, then Oracle Database takes it offline immediately.

- If the rollback segment does contain information for active transactions, then the database makes the rollback segment unavailable for future transactions and takes it offline after all the active transactions are committed or rolled back.

When the rollback segment is offline, it can be brought online by any instance.

To see whether a rollback segment is online or offline, query `STATUS` column of the data dictionary view `DBA_ROLLBACK_SEGS`. Online rollback segments have a value of `IN_USE`. Offline rollback segments have a value of `AVAILABLE`.

Restriction on Taking Rollback Segments Offline You cannot take the `SYSTEM` rollback segment offline.

storage_clause

Use the `storage_clause` to change the storage characteristics of the rollback segment.

Restrictions on Rollback Segment Storage You cannot change the value of `INITIAL` parameter. If the rollback segment is in a locally managed tablespace, then the only storage parameter you can change is `OPTIMAL`. If the rollback segment is in a dictionary-managed tablespace, then the only storage parameters you can change are `NEXT`, `MINEXTENTS`, `MAXEXTENTS` and `OPTIMAL`.

See Also: storage_clause for syntax and additional information

SHRINK Clause

Specify `SHRINK` if you want Oracle Database to attempt to shrink the rollback segment to an optimal or specified size. The success and amount of shrinkage depend on the available free space in the rollback segment and how active transactions are holding space in the rollback segment.

If you do not specify `TO` `size_clause`, then the size defaults to the `OPTIMAL` value of the `storage_clause` of the `CREATE ROLLBACK SEGMENT` statement that created the rollback segment. If `OPTIMAL` was not specified, then the size defaults to the `MINEXTENTS` value of the `storage_clause` of the `CREATE ROLLBACK SEGMENT` statement.

Regardless of whether you specify `TO` `size_clause`:

- The value to which Oracle Database shrinks the rollback segment is valid for the execution of the statement. Thereafter, the size reverts to the `OPTIMAL` value of the `CREATE ROLLBACK SEGMENT` statement.

- The rollback segment cannot shrink to less than two extents.

To determine the actual size of a rollback segment after attempting to shrink it, query the `BYTES`, `BLOCKS`, and `EXTENTS` columns of the `DBA_SEGMENTS` view.

Restriction on Shrinking Rollback Segments In a Real Application Clusters environment, you can shrink only rollback segments that are online to your instance.

See Also: size_clause for information on that clause, and "Resizing a Rollback Segment: Example"

Examples

The following examples use the `rbs_one` rollback segment, which was created in "Creating a Rollback Segment: Example".

Bringing a Rollback Segment Online: Example This statements brings the rollback segment `rbs_one` online:

    ALTER ROLLBACK SEGMENT rbs_one ONLINE;

Resizing a Rollback Segment: Example This statements shrinks the rollback segment `rbs_one`:

    ALTER ROLLBACK SEGMENT rbs_one
       SHRINK TO 100 M;